package com.pig4cloud.pig.ads.dao;

import com.dy.yunying.api.dto.AdDataDto;
import com.dy.yunying.api.vo.AdDataAnalysisVO;
import com.pig4cloud.pig.api.dto.FreeCrowdPackDto;
import com.pig4cloud.pig.api.vo.FreeCrowdPackVo;
import com.pig4cloud.pig.api.vo.FreeCrowdVo;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.List;
import java.util.Objects;

/**
 * @author ：lile
 * @date ：2021/7/1 20:05
 * @description：
 * @modified By：
 */
@Component
@Slf4j
public class AdFreeCrowdPackDao {

	@Resource(name = "clickhouseTemplate")
	private JdbcTemplate clickhouseTemplate;


	public List<FreeCrowdVo> getFreeCrowdList(FreeCrowdPackDto dto) {
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT\n" +
				"\t\ta.regkid,\n" +
				"\t\ta.pgid,\n" +
				"\t\ta.pgidName,\n" +
				"\t\ta.imei,\n" +
				"\t\ta.idfa,\n" +
				"\t\ta.oaid,\n" +
				"\t\ta.mobile,\n" +
				"\t\ta.mac,\n" +
				"\t\tSUM(a.payfee) payfee,\n" +
				"\t\tSUM(a.activenum) activenum,\n" +
				"\t\tMAX(a.activemaxnum) activemaxnum\n" +
				"\t\tFROM");
		sql.append(" (\n" +
				"\t\tSELECT\n" +
				"\t\tc.regkid,\n" +
				"\t\tc.pgid,\n" +
				"\t\tc.pgidName,\n" +
				"\t\tc.mobile,\n" +
				"\t\tc.imei,\n" +
				"\t\tc.idfa,\n" +
				"\t\tc.oaid,\n" +
				"\t\tc.mac,\n" +
				"\t\tc.num,\n" +
				"\t\tCOUNT(DISTINCT c.active_dif) activenum,\n" +
				"\t\tCOUNT(c.num) activemaxnum,\n" +
				"\t\tsum(c.fee + c.givemoney) payfee\n" +
				"\t\tfrom (\n" +
				"\t\tSELECT\n" +
				"\t\tb.day,\n" +
				"\t\tb.activeDay,\n" +
				"\t\tb.pgid,\n" +
				"\t\tb.pgidName,\n" +
				"\t\tb.imei,\n" +
				"\t\tb.idfa,\n" +
				"\t\tb.oaid,\n" +
				"\t\tb.mac,\n" +
				"\t\tb.regkid,\n" +
				"\t\tb.paykid,\n" +
				"\t\tb.mobile,\n" +
				"\t\tb.activekid,\n" +
				"\t\tb.givemoney,\n" +
				"\t\tb.fee,\n" +
				"\t\tb.active_dif,\n" +
				"\t\trowNumberInAllBlocks()+ 1 as ranknum,\n" +
				"\t\tranknum - active_dif num\n" +
				"\t\tfrom(\n" +
				"\t\tSELECT\n" +
				"\t\treg.`day` as day,\n" +
				"\t\tuc.day as activeDay,\n" +
				"\t\treg.pgid pgid,\n" +
				"\t\tpg.gname pgidName,\n" +
				"\t\treg.imei imei,\n" +
				"\t\treg.idfa idfa,\n" +
				"\t\treg.oaid oaid,\n" +
				"\t\treg.mac mac,\n" +
				"\t\treg.kid regkid,\n" +
				"\t\tuc.regkid activekid,\n" +
				"\t\tud.mobile mobile,\n" +
				"\t\trc.regkid paykid,\n" +
				"\t\trc.givemoney givemoney,\n" +
				"\t\trc.fee fee,\n" +
				"-- \t\tdateDiff('day', parseDateTimeBestEffort(toString(day)), parseDateTimeBestEffort(toString(activeDay))) as\n" +
				"\t\tuc.active_dif active_dif\n" +
				"\t\tfrom thirty_game_device_reg reg\n" +
				"\t\tleft join odsmysql_parent_game pg\n" +
				"\t\ton reg.pgid = pg.id\n" +
				"\t\tLEFT JOIN (\n" +
				"\t\tselect\n" +
				"\t\targMax(reg.day, reg.receivetime) day,\n" +
				"\t\targMax(rc.day, reg.receivetime) payday,\n" +
				"\t\targMax(rc.uuid, reg.receivetime) AS uuid,\n" +
				"\t\targMax(rc.fee, reg.receivetime) fee,\n" +
				"\t\targMax(rc.givemoney, reg.receivetime) givemoney,\n" +
				"\t\targMax(reg.gameid, reg.receivetime) AS gameid,\n" +
				"\t\targMax(reg.pgid, reg.receivetime) pgid,\n" +
				"\t\targMax(reg.kid, reg.receivetime) regkid\n" +
				"\t\tfrom original_user_recharge rc\n" +
				"\t\tleft join odsmysql_wan_game g\n" +
				"\t\ton rc.gameid=g.id\n" +
				"\t\tLEFT join thirty_game_device_reg reg\n" +
				"\t\ton rc.uuid=reg.uuid\n" +
				"\t\tand g.pgid = reg.pgid\n");
		sql.append(this.selectCondition(dto, "reg"));
		sql.append(" AND rc.`day` >= reg.`day`\n" +
				"\t\tgroup by rc.kid ) rc\n" +
				"\t\ton rc.regkid = reg.kid ");
		sql.append(" LEFT JOIN (\n" +
				"\t\t-- 新增设备上注册的账号\n" +
				"\t\tselect\n" +
				"\t\tur.kid ur_kid,\n" +
				"\t\targMax(dr.kid, dr.receivetime) dr_kid,\n" +
				"\t\targMax(ur.usrname, dr.receivetime) usrname,\n" +
				"\t\targMax(our.mobile, dr.receivetime) mobile\n" +
				"\t\tfrom v_game_account_reg ur\n" +
				"\t\tLEFT join odsmysql_wan_game g\n" +
				"\t\ton ur.gameid = g.id\n" +
				"\t\tLEFT join odsmysql_wan_user_new our\n" +
				"\t\ton ur.usrname = our.username\n" +
				"\t\tLEFT join thirty_game_device_reg dr\n" +
				"\t\ton ur.uuid = dr.uuid\n" +
				"\t\tand g.pgid = dr.pgid\n");
		sql.append(this.selectCondition(dto, "dr"));
		sql.append(" and ur.receivetimes >= dr.receivetime ");
		sql.append(" group by ur.kid\n" +
				"\t\t) ud\n" +
				"\t\ton ud.dr_kid = reg.kid ");
		sql.append(" LEFT JOIN\n" +
				"\t\t( select ub.kid ubkid,\n" +
				"\t\targMax(reg.day, reg.receivetime) regday,\n" +
				"\t\targMax(ub.day, reg.receivetime) day,\n" +
				"\t\targMax(ub.uuid, reg.receivetime) AS uuid,\n" +
				"\t\targMax(reg.gameid, reg.receivetime) AS gameid,\n" +
				"\t\targMax(reg.pgid, reg.receivetime) pgid,\n" +
				"\t\targMax(reg.kid, reg.receivetime) regkid,\n" +
				"\t\tdateDiff('day', parseDateTimeBestEffort(toString(regday)), parseDateTimeBestEffort(toString(day))) as\n" +
				"\t\tactive_dif\n" +
				"\t\tfrom user_behavior ub\n" +
				"\t\tleft join odsmysql_wan_game g\n" +
				"\t\ton ub.gameid = g.id\n" +
				"\t\tLEFT join thirty_game_device_reg reg\n" +
				"\t\ton ub.uuid = reg.uuid\n" +
				"\t\tand g.pgid = reg.pgid\n" +
				"\t\twhere 1=1 and ub.receivetime >= reg.receivetime\n" +
				"\t\tand ub.day >=reg.day ");
		if (Objects.nonNull(dto.getStartDate())) {
			sql.append("AND ub.day >= ").append(dto.getStartDate());
		}
		sql.append(" GROUP BY ub.kid\n" +
				"\t\t) uc\n" +
				"\t\ton reg.kid = uc.regkid ");
		sql.append(this.selectCondition(dto, "reg"));
		sql.append(" group  by\n" +
				"\t\treg.`day`,\n" +
				"\t\tuc.day,\n" +
				"\t\treg.pgid,\n" +
				"\t\tpg.gname,\n" +
				"\t\treg.imei,\n" +
				"\t\treg.idfa,\n" +
				"\t\treg.oaid,\n" +
				"\t\treg.mac,\n" +
				"\t\treg.kid,\n" +
				"\t\tuc.regkid,\n" +
				"\t\tud.mobile,\n" +
				"\t\trc.regkid,\n" +
				"\t\trc.givemoney,\n" +
				"\t\trc.fee,\n" +
				"\t\tactive_dif\n" +
				"\t\torder by reg.kid,uc.active_dif ASC\n" +
				"\t\t)b ");
		sql.append(" )c ");
		sql.append(this.selectCondition(dto, " "));
		sql.append(" group by\n" +
				"\t\tc.pgid,\n" +
				"\t\tc.pgidName,\n" +
				"\t\tc.imei,\n" +
				"\t\tc.idfa,\n" +
				"\t\tc.oaid,\n" +
				"\t\tc.mac,\n" +
				"\t\tc.regkid,\n" +
				"\t\tc.mobile,\n" +
				"\t\tc.num) as a\n" +
				"\t\tgroup by\n" +
				"\t\ta.regkid,\n" +
				"\t\ta.pgid,\n" +
				"\t\ta.pgidName,\n" +
				"\t\ta.imei,\n" +
				"\t\ta.idfa,\n" +
				"\t\ta.oaid,\n" +
				"\t\ta.mobile,\n" +
				"\t\ta.mac ");
		sql.append(" HAVING 1=1 ");
		sql.append(this.querySql(dto));
		log.info("自有包查询.sql:[{}]", sql.toString());
		List<FreeCrowdVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(FreeCrowdVo.class));
		return list;
	}

	//打包列表
	public List<FreeCrowdPackVo> getFreeCrowdListPack(FreeCrowdPackDto dto) {
		StringBuilder sql = new StringBuilder();

		sql.append("SELECT\n" +
				"\t\ta.regkid,\n" +
				"\t\ta.pgid,\n" +
				"\t\ta.pgidName,\n" +
				"\t\ta.imei,\n" +
				"\t\ta.idfa,\n" +
				"\t\ta.oaid,\n" +
				"\t\ta.mobile,\n" +
				"\t\ta.mac,\n" +
				"\t\tSUM(a.payfee) payfee,\n" +
				"\t\tSUM(a.activenum) activenum,\n" +
				"\t\tMAX(a.activemaxnum) activemaxnum\n" +
				"\t\tFROM");
		sql.append(" (\n" +
				"\t\tSELECT\n" +
				"\t\tc.regkid,\n" +
				"\t\tc.pgid,\n" +
				"\t\tc.pgidName,\n" +
				"\t\tc.mobile,\n" +
				"\t\tc.imei,\n" +
				"\t\tc.idfa,\n" +
				"\t\tc.oaid,\n" +
				"\t\tc.mac,\n" +
				"\t\tc.num,\n" +
				"\t\tCOUNT(DISTINCT c.active_dif) activenum,\n" +
				"\t\tCOUNT(c.num) activemaxnum,\n" +
				"\t\tsum(c.fee + c.givemoney) payfee\n" +
				"\t\tfrom (\n" +
				"\t\tSELECT\n" +
				"\t\tb.day,\n" +
				"\t\tb.activeDay,\n" +
				"\t\tb.pgid,\n" +
				"\t\tb.pgidName,\n" +
				"\t\tb.imei,\n" +
				"\t\tb.idfa,\n" +
				"\t\tb.oaid,\n" +
				"\t\tb.mac,\n" +
				"\t\tb.regkid,\n" +
				"\t\tb.paykid,\n" +
				"\t\tb.mobile,\n" +
				"\t\tb.activekid,\n" +
				"\t\tb.givemoney,\n" +
				"\t\tb.fee,\n" +
				"\t\tb.active_dif,\n" +
				"\t\trowNumberInAllBlocks()+ 1 as ranknum,\n" +
				"\t\tranknum - active_dif num\n" +
				"\t\tfrom(\n" +
				"\t\tSELECT\n" +
				"\t\treg.`day` as day,\n" +
				"\t\tuc.day as activeDay,\n" +
				"\t\treg.pgid pgid,\n" +
				"\t\tpg.gname pgidName,\n" +
				"\t\treg.imei imei,\n" +
				"\t\treg.idfa idfa,\n" +
				"\t\treg.oaid oaid,\n" +
				"\t\treg.mac mac,\n" +
				"\t\treg.kid regkid,\n" +
				"\t\tuc.regkid activekid,\n" +
				"\t\tud.mobile mobile,\n" +
				"\t\trc.regkid paykid,\n" +
				"\t\trc.givemoney givemoney,\n" +
				"\t\trc.fee fee,\n" +
				"-- \t\tdateDiff('day', parseDateTimeBestEffort(toString(day)), parseDateTimeBestEffort(toString(activeDay))) as\n" +
				"\t\tuc.active_dif active_dif\n" +
				"\t\tfrom thirty_game_device_reg reg\n" +
				"\t\tleft join odsmysql_parent_game pg\n" +
				"\t\ton reg.pgid = pg.id\n" +
				"\t\tLEFT JOIN (\n" +
				"\t\tselect\n" +
				"\t\targMax(reg.day, reg.receivetime) day,\n" +
				"\t\targMax(rc.day, reg.receivetime) payday,\n" +
				"\t\targMax(rc.uuid, reg.receivetime) AS uuid,\n" +
				"\t\targMax(rc.fee, reg.receivetime) fee,\n" +
				"\t\targMax(rc.givemoney, reg.receivetime) givemoney,\n" +
				"\t\targMax(reg.gameid, reg.receivetime) AS gameid,\n" +
				"\t\targMax(reg.pgid, reg.receivetime) pgid,\n" +
				"\t\targMax(reg.kid, reg.receivetime) regkid\n" +
				"\t\tfrom original_user_recharge rc\n" +
				"\t\tleft join odsmysql_wan_game g\n" +
				"\t\ton rc.gameid=g.id\n" +
				"\t\tLEFT join thirty_game_device_reg reg\n" +
				"\t\ton rc.uuid=reg.uuid\n" +
				"\t\tand g.pgid = reg.pgid\n");
		sql.append(this.selectCondition(dto, "reg"));
		sql.append(" AND rc.`day` >= reg.`day`\n" +
				"\t\tgroup by rc.kid ) rc\n" +
				"\t\ton rc.regkid = reg.kid ");
		sql.append(" LEFT JOIN (\n" +
				"\t\t-- 新增设备上注册的账号\n" +
				"\t\tselect\n" +
				"\t\tur.kid ur_kid,\n" +
				"\t\targMax(dr.kid, dr.receivetime) dr_kid,\n" +
				"\t\targMax(ur.usrname, dr.receivetime) usrname,\n" +
				"\t\targMax(our.mobile, dr.receivetime) mobile\n" +
				"\t\tfrom v_game_account_reg ur\n" +
				"\t\tLEFT join odsmysql_wan_game g\n" +
				"\t\ton ur.gameid = g.id\n" +
				"\t\tLEFT join odsmysql_wan_user_new our\n" +
				"\t\ton ur.usrname = our.username\n" +
				"\t\tLEFT join thirty_game_device_reg dr\n" +
				"\t\ton ur.uuid = dr.uuid\n" +
				"\t\tand g.pgid = dr.pgid\n");
		sql.append(this.selectCondition(dto, "dr"));
		sql.append(" and ur.receivetimes >= dr.receivetime ");
		sql.append(" group by ur.kid\n" +
				"\t\t) ud\n" +
				"\t\ton ud.dr_kid = reg.kid ");
		sql.append(" LEFT JOIN\n" +
				"\t\t( select ub.kid ubkid,\n" +
				"\t\targMax(reg.day, reg.receivetime) regday,\n" +
				"\t\targMax(ub.day, reg.receivetime) day,\n" +
				"\t\targMax(ub.uuid, reg.receivetime) AS uuid,\n" +
				"\t\targMax(reg.gameid, reg.receivetime) AS gameid,\n" +
				"\t\targMax(reg.pgid, reg.receivetime) pgid,\n" +
				"\t\targMax(reg.kid, reg.receivetime) regkid,\n" +
				"\t\tdateDiff('day', parseDateTimeBestEffort(toString(regday)), parseDateTimeBestEffort(toString(day))) as\n" +
				"\t\tactive_dif\n" +
				"\t\tfrom user_behavior ub\n" +
				"\t\tleft join odsmysql_wan_game g\n" +
				"\t\ton ub.gameid = g.id\n" +
				"\t\tLEFT join thirty_game_device_reg reg\n" +
				"\t\ton ub.uuid = reg.uuid\n" +
				"\t\tand g.pgid = reg.pgid\n" +
				"\t\twhere 1=1 and ub.receivetime >= reg.receivetime\n" +
				"\t\tand ub.day >=reg.day ");
		if (Objects.nonNull(dto.getStartDate())) {
			sql.append("AND ub.day >= ").append(dto.getStartDate());
		}
		sql.append(" GROUP BY ub.kid\n" +
				"\t\t) uc\n" +
				"\t\ton reg.kid = uc.regkid ");
		sql.append(this.selectCondition(dto, "reg"));
		sql.append(" group  by\n" +
				"\t\treg.`day`,\n" +
				"\t\tuc.day,\n" +
				"\t\treg.pgid,\n" +
				"\t\tpg.gname,\n" +
				"\t\treg.imei,\n" +
				"\t\treg.idfa,\n" +
				"\t\treg.oaid,\n" +
				"\t\treg.mac,\n" +
				"\t\treg.kid,\n" +
				"\t\tuc.regkid,\n" +
				"\t\tud.mobile,\n" +
				"\t\trc.regkid,\n" +
				"\t\trc.givemoney,\n" +
				"\t\trc.fee,\n" +
				"\t\tactive_dif\n" +
				"\t\torder by reg.kid,uc.active_dif ASC\n" +
				"\t\t)b ");
		sql.append(" )c ");
		sql.append(this.selectCondition(dto, " "));
		sql.append(" group by\n" +
				"\t\tc.pgid,\n" +
				"\t\tc.pgidName,\n" +
				"\t\tc.imei,\n" +
				"\t\tc.idfa,\n" +
				"\t\tc.oaid,\n" +
				"\t\tc.mac,\n" +
				"\t\tc.regkid,\n" +
				"\t\tc.mobile,\n" +
				"\t\tc.num) as a\n" +
				"\t\tgroup by\n" +
				"\t\ta.regkid,\n" +
				"\t\ta.pgid,\n" +
				"\t\ta.pgidName,\n" +
				"\t\ta.imei,\n" +
				"\t\ta.idfa,\n" +
				"\t\ta.oaid,\n" +
				"\t\ta.mobile,\n" +
				"\t\ta.mac ");
		sql.append(" HAVING 1=1 ");
		sql.append(this.querySql(dto));
		log.info("自有包查询.sql:[{}]", sql.toString());
		List<FreeCrowdPackVo> list = clickhouseTemplate.query(sql.toString(), new Object[]{},
				new BeanPropertyRowMapper<>(FreeCrowdPackVo.class));
		return list;
	}

	//筛选条件
	public String selectCondition(FreeCrowdPackDto req, String bieming) {
		StringBuilder sqlCondition = new StringBuilder();

		//主游戏
		String pgidArr = req.getPgidArr();

		//日期
		Integer sTime = req.getStartDate();
		Integer eTime = req.getEndDate();
		sqlCondition.append("where 1=1");
		if (StringUtils.isBlank(bieming)) {
			if (Objects.nonNull(sTime) && Objects.nonNull(eTime)) {
				sqlCondition.append(" and ").append("day  >= ").append(sTime);
				sqlCondition.append(" and ").append("day  <= ").append(eTime);
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				sqlCondition.append(" and ").append("pgid IN (").append(pgidArr).append(")");
			}
		} else {
			if (Objects.nonNull(sTime) && Objects.nonNull(eTime)) {
				sqlCondition.append(" and ").append(bieming).append(".day  >= ").append(sTime);
				sqlCondition.append(" and ").append(bieming).append(".day  <= ").append(eTime);
			}
			if (StringUtils.isNotBlank(pgidArr)) {
				sqlCondition.append(" and ").append(bieming).append(".pgid IN (").append(pgidArr).append(")");
			}
		}

		return sqlCondition.toString();
	}

	//活跃搜索
	public String querySql(FreeCrowdPackDto req) {
		StringBuilder sqlCondition = new StringBuilder();
		BigDecimal startPay = req.getStartPay();
		BigDecimal endPay = req.getEndPay();
		Integer activeStartNum = req.getActiveStartNum();
		Integer activeEndNum = req.getActiveEndNum();
		Integer activeStartMaxNum = req.getActiveStartMaxNum();
		Integer activeEndMaxNum = req.getActiveEndMaxNum();


		if (Objects.nonNull(startPay) && Objects.nonNull(endPay)) {
			sqlCondition.append(" and payfee BETWEEN ").append(startPay).append(" and ").append(endPay).append("\n");
		}
		if (Objects.nonNull(activeStartNum) && Objects.nonNull(activeEndNum)) {
			sqlCondition.append(" and activenum BETWEEN  ").append(activeStartNum).append(" and ").append(activeEndNum).append("\n");
		}
		if (Objects.nonNull(activeStartMaxNum) && Objects.nonNull(activeEndMaxNum)) {
			sqlCondition.append(" and activemaxnum BETWEEN  ").append(activeStartMaxNum).append(" and ").append(activeEndMaxNum).append("\n");
		}


		return sqlCondition.toString();
	}
}
